Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Text

Imports Krystalware.SlickUpload
Imports Krystalware.SlickUpload.Configuration
Imports Krystalware.SlickUpload.Storage

Namespace Storage
    Public Class CustomSqlUploadStreamProvider
        Inherits SqlClientUploadStreamProvider

        Public Sub New(settings As UploadStreamProviderElement)
            MyBase.New(settings)
        End Sub

        ''' <summary>
        ''' Inserts the record for the given file, and returns the id of the record inserted.
        ''' </summary>
        ''' <remarks>This is a very verbose implementation, because it supports every possible data type and doesn't use an ORM. When implementing in your solution,
        ''' we recommend you rewrite using whatever data access layer you use.</remarks>
        Public Overrides Function InsertRecord(file As UploadedFile, cn As IDbConnection, t As IDbTransaction) As String
            Dim category As String = file.UploadRequest.Data("fileCategory")

            Using cmd As SqlCommand = DirectCast(cn.CreateCommand(), SqlCommand)
                Dim insertCommand As StringBuilder = New StringBuilder()

                insertCommand.Append("INSERT INTO ")
                insertCommand.Append(Table)
                insertCommand.Append(" (")
                insertCommand.Append(DataField)

                If Not FileNameField Is Nothing Then
                    insertCommand.Append(",")
                    insertCommand.Append(FileNameField)
                End If

                insertCommand.Append(",")
                insertCommand.Append("Category")

                If Not DataType = SqlColumnDataType.FileStream Then
                    insertCommand.Append(") VALUES (NULL")
                Else
                    insertCommand.Append(") OUTPUT INSERTED." + KeyField)
                    insertCommand.Append(" VALUES (CAST('' AS varbinary(MAX))")
                End If

                If Not FileNameField Is Nothing Then
                    insertCommand.Append(",@fileName")

                    Dim fileNameParm As SqlParameter = cmd.CreateParameter()

                    fileNameParm.ParameterName = "@fileName"
                    fileNameParm.DbType = DbType.String
                    fileNameParm.Value = file.ClientName

                    cmd.Parameters.Add(fileNameParm)
                End If

                insertCommand.Append(",@category")

                Dim categoryParm As SqlParameter = cmd.CreateParameter()

                categoryParm.ParameterName = "@category"
                categoryParm.DbType = DbType.String
                categoryParm.Value = category

                cmd.Parameters.Add(categoryParm)

                insertCommand.Append(")")

                If Not DataType = SqlColumnDataType.FileStream Then
                    insertCommand.Append("SELECT SCOPE_IDENTITY()")
                End If

                cmd.CommandText = insertCommand.ToString()
                cmd.Transaction = CType(t, SqlTransaction)

                Try
                    If Not cn.State = ConnectionState.Open Then
                        cn.Open()
                    End If

                    Return cmd.ExecuteScalar().ToString()
                Finally
                    If t Is Nothing Then
                        cn.Close()
                    End If
                End Try
            End Using
        End Function
    End Class
End Namespace